package org.example;

import com.grapecity.documents.excel.*;
import com.grapecity.documents.excel.drawing.*;
import org.junit.Test;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;

public class XLSX {
    /**
     * 解析csv字符串
     *
     * @param csvStr csv字符串
     * @return 工作簿对象
     */
    public static Workbook AnalysisCSV(String csvStr) {
        Workbook workbook = new Workbook();
        byte[] arr = csvStr.getBytes(StandardCharsets.UTF_8);
        try (InputStream s = new ByteArrayInputStream(arr)) {
            workbook.open(s, OpenFileFormat.Csv);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 处理源数据，形成可视化图表
     *
     * @param workbook 工作簿对象
     */
    public static void processWorkbook(Workbook workbook) {
        IWorksheet worksheet = workbook.getWorksheets().get(0);
        // 把第K列数据移动到B列位置
        worksheet.getRange("B:B").insert();
        worksheet.getRange("K:K").copy(worksheet.getRange("B:B"));
        worksheet.getRange("K:K").delete();

        // 获取数据范围
        IRange usedRange = worksheet.getUsedRange();

        // 创建表格
        ITable addTable = worksheet.getTables().add(usedRange, true);
        addTable.setName("每月比特币数据");
        usedRange.autoFit();

        // 创建图表
        IShape iShape = worksheet.getShapes().addChart(ChartType.StockVOHLC, 0, 0, usedRange.getWidth(), usedRange.getHeight());
        IChart chart = iShape.getChart();
        chart.getChartTitle().setText("比特币每月开盘-最高-最低-收盘-成交量");
        chart.getSeriesCollection().add(worksheet.getRange(0, 0, usedRange.getRowCount(), 6), RowCol.Columns, true, true);
        IAxis categoryAxis = chart.getAxes().item(AxisType.Category);
        categoryAxis.setBaseUnit(TimeUnit.Months);
        categoryAxis.getTickLabels().setOrientation(45);
        categoryAxis.getTickLabels().setNumberFormat("d/m/yyyy");

        ITrendline voltrend = chart.getSeriesCollection().get(0).getTrendlines().add();
        voltrend.setName("3个月移动平均成交量");
        voltrend.setType(TrendlineType.MovingAvg);
        voltrend.setPeriod(3);
        voltrend.getFormat().getLine().getColor().setRGB(Color.GetBlue());
        voltrend.getFormat().getLine().setDashStyle(LineDashStyle.RoundDot);

        ITrendline hightrend = chart.getSeriesCollection().get(2).getTrendlines().add();
        hightrend.setName("3个月移动平均最高价");
        hightrend.setType(TrendlineType.MovingAvg);
        hightrend.setPeriod(3);
        hightrend.getFormat().getLine().getColor().setRGB(Color.GetGreen());
        hightrend.getFormat().getLine().setDashStyle(LineDashStyle.RoundDot);

        ITrendline lowtrend = chart.getSeriesCollection().get(3).getTrendlines().add();
        lowtrend.setName("3个月移动平均最低价");
        lowtrend.setType(TrendlineType.MovingAvg);
        lowtrend.setPeriod(3);
        lowtrend.getFormat().getLine().getColor().setRGB(Color.GetRed());
        lowtrend.getFormat().getLine().setDashStyle(LineDashStyle.RoundDot);
    }

    @Test
    public void 测试csv解析成workbook对象() {
        Workbook workbook = AnalysisCSV(CSV.getCsvData());
        System.out.println(workbook.getWorksheets().get(0).getRange(0, 0).getValue());
    }

    @Test
    public void 测试处理了csv数据后的workbook对象() {
        Workbook workbook = AnalysisCSV(CSV.getCsvData());
        processWorkbook(workbook);
        workbook.save("output.xlsx");
    }
}
